package com.springboot.controller;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.springboot.model.DoctorAppointSet;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.alibaba.fastjson.JSON;

public class UploadExcelServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String excelPath = "C:\\Users\\mier\\Desktop\\test.xlsx";
        System.out.println("开始读取excel");
        List<DoctorAppointSet> doctorAppointSets = parseExcel(excelPath);
        //把数据写入数据库
        for(int i=0;i<doctorAppointSets.size();i++)
        {
            DoctorAppointSet doctorAppointSet = doctorAppointSets.get(i);
            System.out.println(doctorAppointSet.toTestString());
        }
    }

    // 解析Excel,读取内容,path Excel路径
    public static List<DoctorAppointSet> parseExcel(String path)
    {
        System.out.println("进入parseExcel函数");
        List<DoctorAppointSet> doctorAppointSets = new ArrayList<>();
        File file = null;
        InputStream input = null;
        if (path != null && path.length() > 7)
        {
            // 判断文件是否是Excel(2003、2007)
            String suffix = path.substring(path.lastIndexOf("."), path.length());
            file = new File(path);
            try {
                input = new FileInputStream(file);
            } catch (FileNotFoundException e) {
                System.out.println("未找到指定的文件！");
            }
            // Excel2003
            if (".xls".equals(suffix))
            {
                POIFSFileSystem fileSystem = null;
                // 工作簿
                HSSFWorkbook workBook = null;
                try {
                    fileSystem = new POIFSFileSystem(input);
                    workBook = new HSSFWorkbook(fileSystem);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                // 获取第一个工作簿
                HSSFSheet sheet = workBook.getSheetAt(0);
                doctorAppointSets = getContent((Sheet) sheet);
            }
            // Excel2007
            else if (".xlsx".equals(suffix))
            {
                XSSFWorkbook workBook = null;
                try {
                    workBook = new XSSFWorkbook(input);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                // 获取第一个工作簿
                XSSFSheet sheet = workBook.getSheetAt(0);
                doctorAppointSets = getContent(sheet);
            }
        }
        else {
            System.out.println("非法的文件路径!");
        }
        return doctorAppointSets;
    }

    // 获取Excel内容
    public static List<DoctorAppointSet> getContent(Sheet sheet)
    {
        List<DoctorAppointSet> doctorAppointSets= new ArrayList<>();
        // Excel数据总行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        int cellCountAll = sheet.getRow(0).getPhysicalNumberOfCells();
        // 遍历数据行，略过标题行，从第二行开始
        for (int i = 1; i < rowCount; i++)
        {
            DoctorAppointSet doctorAppointSet=new DoctorAppointSet();
            Row row = sheet.getRow(i);
            // 遍历行单元格
            for (int j = 0; j < cellCountAll; j++)
            {
                Cell cell = row.getCell(j);
                if (cell == null)
                {
                    row.createCell(j).setCellValue("");
                }
                else {
                    // 根据cell中的类型来输出数据
                    switch (j)
                    {
                        case 0:
                            if (cell.getCellType() == CellType.NUMERIC) {
                                doctorAppointSet.setDoctorId((int) cell.getNumericCellValue());
                            }
                            break;
                        case 1:
                            if (cell.getCellStyle().getDataFormat() == 14) {
                                doctorAppointSet.setBookDate(cell.getDateCellValue());
                            }
                            break;
                        case 2:
                            if (cell.getCellType() == CellType.NUMERIC) {
                                doctorAppointSet.setMorningNum((int) cell.getNumericCellValue());
                            }
                            break;
                        case 3:
                            if (cell.getCellType() == CellType.NUMERIC) {
                                doctorAppointSet.setAfternoonNum((int) cell.getNumericCellValue());
                            }
                            break;
                        default:break;
                    }
                }
            }
            doctorAppointSets.add(doctorAppointSet);
        }
        return doctorAppointSets;
    }
}
